<?php
/**
 * ADODB 接口
 * $Id: db.php 9 2009-04-26 06:35:12Z bole.Chen $
 */

require_once ROOT.'includes'.DS.'classes'.DS.'adodb'.DS.'adodb.inc.php';

/**
 * ADODB 接口
 *
 * 做为对ADODB操作的中间层，目的是为了使项目应用中不出现具体的SQL
 *
 * @access      public
 */
class DB extends Object {

    var $_db;
    var $dbtype;
    var $lastsql;
    var $debug = false;
    var $host;
    var $user;
    var $pass;
    var $dba;
    var $rs;
    var $cachedir;
    var $querytime  = 0;
    var $querylog   = array();
    var $querydebug = array();
    var $cacheflush = false;    //为真时，所有的 cache 开头的函数变成 cacheflush

    var $server;

    function __construct( $db = 'mysql', $cachedir = null ) {
        if (PHP_VERSION >= 5) parent::Object();

        if ($cachedir == null) $cachedir = ROOT.'cache'.DS.'db';
        $this->cache_dir = $GLOBALS['ADODB_CACHE_DIR'] = $cachedir;
        if (!is_dir($this->cache_dir)) @mkdir($this->cache_dir, 0777);

        $this->dbtype           =   &$db;
        $this->_db              =   &NewADOConnection($db);
        $this->_db->debug       =   $this->debug;
        $this->_db->cacheSecs   =   3600*24*30;

        $this->_db->SetFetchMode(ADODB_FETCH_ASSOC);

        //查询统计
        $this->_db->fnExecute       = 'CountQueries';
        $this->_db->fnCacheExecute  = 'CountCachedQueries';

        $this->server = & $_SERVER;

        return $this->_db;
    }

    // Shutdown function
    function __destruct() {
        //$this->debugmsg('DB::close');
        //$this->close();
    }

    /**
     * 连接数据库
     *
     * @access     public
     * @return     bool
    */
    function connect($host='localhost', $user='root', $pass='', $dba='') {
        $this->host     = &$host;
        $this->user     = &$user;
        $this->pass     = &$pass;
        $this->dba      = &$dba;

        return @$this->_db->Connect($host, $user, $pass, $dba) or die('<b>DB::connect</b> error: '.$this->errormsg());
    }

    function pconnect($host='localhost', $user='root', $pass='', $dba='') {
        $this->host     = &$host;
        $this->user     = &$user;
        $this->pass     = &$pass;
        $this->dba      = &$dba;

        return $this->_db->Pconnect($host, $user, $pass, $dba);
    }

    /**
     * 统计记录数
     *
     * @param      none
     * @access     public
     * @return     void
    */
    function count($table, $where='', $field='*') {
       return $this->getone($table, "COUNT($field)", $where);
    }

    /**
     * 获得第一个值
     *
     * @access     public
     * @return     void
    */
    function getone($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));

        return $this->_db->GetOne($this->lastsql);
    }

    /**
     * 缓冲获得第一个值
     *
     * @access     public
     * @return     void
    */
    function cachegetone($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
        if ($this->cacheflush) return $this->cacheflush();
        return $this->_db->CacheGetOne($this->lastsql);
    }

    /**
     * 获得一行
     *
     * @access     public
     * @return     void
    */
    function getrow($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));

        return $this->_db->GetRow($this->lastsql);
    }

    /**
     * 缓冲获得一行
     *
     * @access     public
     * @return     void
    */
    function cachegetrow($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
        if ($this->cacheflush) return $this->cacheflush();
        return $this->_db->CacheGetRow($this->lastsql);
    }

    /**
     * 获得一列
     *
     * @access     public
     * @return     void
    */
    function getcol($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));

        return $this->_db->GetCol($this->lastsql);
    }

    /**
     * 缓冲获得一列
     *
     * @access     public
     * @return     void
    */
    function cachegetcol($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
        if ($this->cacheflush) return $this->cacheflush();
        return $this->_db->CacheGetCol($this->lastsql);
    }

    /**
     * 获取全部查询数组
     *
     * @access     public
     * @return     void
    */
    function getall($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));

        return $this->_db->GetAll($this->lastsql);
    }

    /**
     * 缓冲获取全部查询数组
     *
     * @access     public
     * @return     void
    */
    function cachegetall($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
        if ($this->cacheflush) return $this->cacheflush();
        return $this->_db->CacheGetAll($this->lastsql);
    }

    /**
     * &Execute()
     *
     * @access     public
     * @return     void
    */
    function q($table, $field='*', $where='') {
        return $this->execute($table, $field, $where);
    }

    /**
     * &CacheExecute()
     *
     * @access     public
     * @return     void
    */
    function cacheq($table, $field='*', $where='') {
        return $this->cacheexecute($table, $field, $where);
    }

    /**
     * &CacheExecute()
     *
     * @access     public
     * @return     void
    */
    function cachequery($table, $field='*', $where='') {
        return $this->cacheexecute($table, $field, $where);
    }

    /**
     * &Execute()
     *
     * @access     public
     * @return     void
    */
    function query($table, $field='*', $where='') {
        return $this->execute($table, $field, $where);
    }

    /**
     * 查询
     *
     * @access     public
     * @return     void
    */
    function &execute($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
        $this->rs =& $this->_db->Execute($this->lastsql);

        return $this->_fetch();
    }

    /**
     * 缓冲查询
     *
     * @access     public
     * @return     void
    */
    function &cacheexecute($table, $field='*', $where='') {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
        if ($this->cacheflush) return $this->cacheflush();
        $this->rs =& $this->_db->CacheExecute($this->lastsql);

        return $this->_fetch();
    }

    /**
     * 分页查询
     * $out['list']=$db->getpage(array("table","*","xxx=1"),$out['page'],$s,$p,$url)
     *
     * @param     array $excute 用于查询的array，格式同getall
     * @param     $excute 用于查询的array，格式同getall
     * @param     &$page 输出给模版的分页信息
     * @param     int $s 每页显示数，默认可以自动获取_GET['_s']
     * @param     int $p 当前页，默认可以自动获取_GET['p']
     * @param     string $url 分页转到的页面，默认为当前页面
     * @access     public
     * @return     void
    */
    function getpage($execute, &$page, $s="", $p="",$url="") {
        $table  = $execute[0];          //表
        $field  = $execute[1];          //字段
        $where  = $execute[2];          //查询条件
        if (!$field) $field = '*';
        $countfield = preg_match('/[,\.]/', $field) ? '*' : $field; //统计总数时的字段

        $countwhere         = preg_replace("/ORDER\s+BY\s+.+?(LIMIT|$)/i", "\\1", $where);//清除排序信息
        //对于 GROUP BY 的特殊处理
        if (preg_match('/GROUP\s+BY\s+([^\s]+)/i', $countwhere, $match)) {
            $countwhere     = preg_replace('/GROUP\s+BY.+$/i', '', $countwhere);
            $page['total']  = $this->getone($table, "COUNT(DISTINCT({$match[1]}))", $countwhere);
        } else {
            $page['total']  = $this->count($table, $countwhere, $countfield);            //$page['total'] 符合条件的记录总数
        }

        //$page['_s'] = $s ? $s : ((int)$this->get['_s'] ? (int)$this->get['_s'] : ((int)$_COOKIE['_s'] ? (int)$_COOKIE['_s'] : 20)); //$page['_s'] 每页显示的记录数，默认读取GET[_s]，如不存在则定为20
        $page['_s'] = $s ? $s : ((int)$this->request['_s'] ? (int)$this->request['_s'] : 20);

        //如果不是由get或cookie传过来的(即在模板中直接写死掉的)，则不记录
        //if (!$s) setcookie('_s', $page['_s'], time()+3600*24);

        if ($page['_s'] > 1000) $page['_s'] = 1000;             //限定每页最大纪录，防止恶意破坏
        $page['p']          = $p ? $p : ((int)$this->request['p'] ? (int)$this->request['p'] : 1);    //$page['p'] 当前页，默认读取GET[p]，如不存在则定为1
        $page['totalpage']  = ceil($page['total'] /  $page['_s']);      //$page['totalpage'] 总共有多少页面
        if (!$this->server['REQUEST_URI']) $this->server['REQUEST_URI'] = sprintf('%s?%s', $this->server['SCRIPT_NAME'], $this->server['QUERY_STRING']);
        if ($this->server['REQUEST_URI'] == '?') $this->server['REQUEST_URI'] = '';
        $page['url']        = $url ? $url : preg_replace("#[&\?](?:_s|p)=\d*#", "", $this->server["REQUEST_URI"]);     //$page['url'] 分页转到的页面，默认为当前页面

        if (preg_match('/\?{1}/', $page['url']))
            $page['url'] .= '&amp;';
        else
            $page['url'] .= '?';

        //for ($i=1; $i<=$page['totalpage']; $i++) $page['list'][$i] = $i;        //$page['list'] 用来显示模版中的分页下拉框

        if ($page['p'] > $page['totalpage']) $page['p'] = $page['totalpage'];
        else if ($page['p'] < $page['totalpage']) $page['next'] = $page['p'] + 1;//$page['next'] 下一页面
        if ($page['p'] > 1) $page['perv'] = $page['p'] - 1;           //$page['perv'] 前一页面
        $start = $page['_s'] * ($page['p'] - 1);
        $page['start']  = ($start >= 0)?$start  + 1:0;          //$page['start'] 起始记录
        $page['end']    = (($start + $page['_s']) < $page['total']) ? $start + $page['_s'] : $page['total'];    //$page['end'] 末尾纪录

        //如果记录为0就不必再查询
        if ($page['total'] > 0) {
            $this->lastsql  = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
            $rs = &$this->_db->SelectLimit($this->lastsql, $page['_s'], $start);
            return $rs->GetArray();
        } else {
            return array();
        }
    }

    /**
     * 获取搜索查询数组
     *
     * @access     public
     * @return     void
     */
    function getsearch($execute, $query, $from) {
        $table  = $execute[0];          //表
        $field  = $execute[1];          //字段
        $where  = $execute[2];          //查询条件

        foreach(explode(',', str_replace(' ','',$from)) as $k=>$v)
        {
            $search .= $search?" or $v like '%$query%'":"$v like '%$query%'";
        }
        $where = $where?$where." and (".$search.")":$search;
        return $this->getall($table, $field, $where);
     }

    /**
     * 获取搜索查询数组(分页)
     *
     * @access     public
     * @return     void
     */
    function getsearchpage($execute, $query, $from, &$page) {
        $table  = $execute[0];          //表
        $field  = $execute[1];          //字段
        $where  = $execute[2];          //查询条件
        foreach(explode(',', str_replace(' ','',$from)) as $k=>$v)
        {
            if ($v) $search .= ($search ? " or $v like '%$query%'" : "$v like '%$query%'");
        }
        $where = ($where ? $where." and (".$search.")" : $search);
        return $this->getpage(array($table, $field, $where),$page);
    }

    /**
     * todo:缓冲分页查询?
     */
    function cachegetpage($execute, &$page, $s="", $p="",$url="") {

    }

    /**
     * Limit查询
     *
     * @access     public
     * @return     void
    */
    function limit($table, $field='*', $where='', $start=0, $pos=10) {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
        $this->rs =& $this->_db->SelectLimit($this->lastsql, $pos, $start);

        return $this->_fetch();
    }

    /**
     * 缓冲Limit
     *
     * @access     public
     * @return     void
    */
    function cachelimit($table, $field='*', $where='', $start=0, $pos=10) {
        $this->lastsql = $this->psql("SELECT $field FROM $table". ($where ? " WHERE $where" : ''));
        if ($this->cacheflush) return $this->cacheflush();
        $this->rs =& $this->_db->CacheSelectLimit($this->lastsql, $pos, $start);

        return $this->_fetch();
    }

    /**
     * SQL查询
     *
     * @access     public
     * @return     void
    */
    function sql($sql) {
        $this->lastsql = $sql;
        $this->rs =& $this->_db->Execute($this->lastsql);

        return $this->_fetch();
    }

    /**
     * 缓冲查询
     *
     * @access     public
     * @return     void
    */
    function cachesql($sql) {
        $this->lastsql = $sql;
        if ($this->cacheflush) return $this->cacheflush();
        $this->rs =& $this->_db->CacheExecute($this->lastsql);

        return $this->_fetch();
    }

    /**
     * 插入
     *
     * @access     public
     * @return     bool
    */
    function insert($table, $array) {
        if (is_array($array)) {
            $nokey = false;
            foreach ($array as $k => $v) {
                if (!$nokey && is_string($k)) {
                    $key[] = $this->_db->nameQuote. $k .$this->_db->nameQuote;
                } else {
                    $nokey = true;
                }

                $value[] = $this->qstr($v);
            }

            $sql = "INSERT INTO $table ". ($key ? '('. join(',', $key). ')' : '') .' VALUES ('. join(',', $value). ')';
            return $this->_sql($sql);
        }
        return false;
    }

    /**
     * 更新
     *
     * @access     public
     * @return     bool
    */
    function update($table, $fields, $where='') {
        if (is_array($fields)) {
            foreach ($fields as $k=>$v) {
                  if(preg_match("/\{\[([\+\-\*\/]{1,2}[\d\.]+)\]\}/", $v, $getit))
                    $field[] = $this->_db->nameQuote. $k .$this->_db->nameQuote. '='. $this->_db->nameQuote. $k .$this->_db->nameQuote.$getit[1];
                else
                    $field[] = $this->_db->nameQuote. $k .$this->_db->nameQuote. '='. $this->qstr($v);
            }

            $this->lastsql = "UPDATE $table SET ". join(',', $field) .($where ? " WHERE $where" : '');
            return $this->_sql($this->lastsql);
        }
        return false;
    }

    function update2($arr, $blank=true) {
        if ($this->rs) {
            return $this->_sql($this->_db->GetUpdateSQL($this->rs, $arr, $blank));
        }
        return false;
    }

    /**
     * 替换操作
     *
     * @access     public
     * @return     bool
    */
    function replace($table, $arr, $key, $quote=true) {
        return $this->_db->Replace($table, $arr, $key, $quote);
    }

    /**
     * 删除
     *
     * @access     public
     * @return     bool
    */
    function delete($table, $where) {
        $this->lastsql = $this->psql("DELETE FROM $table WHERE $where");

        return $this->_sql($this->lastsql);
    }

    /**
     * 标记删除位
     *
     * @access     public
     * @return     bool
    */
    function remove($table, $where, $markfield='delmark') {
        $this->lastsql = $this->psql("UPDATE $table SET $markfield=1 WHERE $where");

        return $this->_sql($this->lastsql);
    }

    /**
     * 关闭链接
     *
     * @access     public
     * @return     bool
    */
    function close() {
        if ($this->rs)  $this->rs->Close();
        if ($this->_db) $this->_db->Close();
    }

    /**
     * 设置缓冲时间
     *
     * @param      none
     * @access     private/public
     * @return     void
    */
    function cachetime($sec=0) {
        if ($sec) $this->_db->cacheSecs = $sec;
    }

    /**
     * 删除缓冲
     *
     * @access     public
     * @return     bool
    */
    function cacheflush() {
        if (preg_match('/\s*SELECT\s+/i', $this->lastsql)) @$this->_db->CacheFlush($this->lastsql);
    }

    /**
     * 获得最后插入ID
     *
     * @access     public
     * @return     void
    */
    function insertid() {
        return $this->_db->Insert_ID();
    }

    /**
     * 返回上一条 SQL 影响的记录数
     *
     * @access     public
     * @return     void
    */
    function affected_rows() {
        return $this->_db->Affected_Rows();
    }

    /**
     * SQL中多字符合并
     *
     * @access     public
     * @return     string
    */
    function concat() {
        $arr = func_get_args();
        $arr = join(',', $arr);
        return $this->_db->Concat($arr);
    }

    /**
     * 预格式化SQL
     *
     * @access     public
     * @return     string
    */
    function psql($sql) {
        $sql = trim($sql);
        if (isset($this->get['debug'])) $this->_debug($sql);
        return $sql;
    }

    /**
     * SQL中引用字符串处理
     *
     * @access     public
     * @return     string
    */
    function qstr($str) {
        return $this->_db->qstr($str);
    }

    /**
     * 调试开关
     *
     * @access     public
     * @return     none
    */
    function debug($onoff=true) {
        $this->_db->debug = $this->debug = $onoff;
    }

    /**
     * 返回最后一次错误号
     *
     * @access     public
     * @return     int
    */
    function errorno() {
        return $this->_db->ErrorNo();
    }

    /**
     * 返回最后一次错误信息
     *
     * @access     public
     * @return     string
    */
    function errormsg() {
        return $this->_db->ErrorMsg();
    }

    function &_sql($sql) {
        $this->lastsql = $sql;
        return $this->_db->Execute($this->lastsql);
    }

    function &_fetch() {
        if (is_object($this->rs)) {
            $i = 0;
            $row['count'] = $this->rs->RecordCount();

            while (!$this->rs->EOF) {
                $row['result'][$i++] = $this->rs->fields;
                $this->rs->MoveNext();
            }
            return $row;
        }
        return false;
    }

    function _debug($query) {
        $this->querylog[]   = $query;

        $mtime              = explode(' ', microtime());
        $starttime          = $mtime[1] + $mtime[0];

        if (substr(trim(strtoupper($query)), 0, 6) == 'SELECT') {
            $data = $this->sql("EXPLAIN $query");
        } else {
            $data = array();
        }

        $mtime              = explode(' ', microtime());
        $querytime          = ($mtime[1] + $mtime[0]) - $starttime;
        $this->querytime   += $querytime;

        $data['querytime']  = $querytime;
        $data['query']      = $query;
        $this->querydebug[] = $data;
    }

}
?>
